﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccessLayer
{
    public class DisbursementHistoryDAL
    {
        StationeryInventoryEntities1 ent;

        public DisbursementHistoryDAL() {
            ent = new StationeryInventoryEntities1();
        }

        public List<object> getDisbursementList(string userName)
        {
            List<object> obj = new List<object>();
            var deptID = (from e in ent.Employees
                          join u in ent.Users
                          on e.UserID equals u.UserID
                          where u.UserName == userName
                          select e.DeptID).SingleOrDefault();

            var disburseList = (from dr in ent.DeptRequisitions
                                where dr.DeptID == deptID
                                && dr.CollectionPointDetailID != null
                                //&& dr.DisbursedBy != null
                                select new { dr.CollectionPointDetail.CollectionDate, dr.CollectionPointDetail.CollectionTime, dr.CollectionPointDetail.CollectionPoint.CollectionPointName, 
                                    dr.CollectedBy, dr.DisbursedBy, dr.CollectionPointDetailID }).OrderByDescending(x => x.CollectionDate);

            obj = disburseList.ToList<object>();
            return obj;
        }

        public List<object> getDisbursementListByDate(String userName, DateTime fromDate, DateTime toDate)
        {
            List<object> obj = new List<object>();
            var deptID = (from e in ent.Employees
                          join u in ent.Users
                          on e.UserID equals u.UserID
                          where u.UserName == userName
                          select e.DeptID).SingleOrDefault();

            var disburseList = (from dr in ent.DeptRequisitions
                                where dr.DeptID == deptID
                                && dr.CollectionPointDetailID != null
                                && dr.CollectionPointDetail.CollectionDate >= fromDate
                                && dr.CollectionPointDetail.CollectionDate <= toDate
                                select new
                                {
                                    dr.CollectionPointDetail.CollectionDate,
                                    dr.CollectionPointDetail.CollectionTime,
                                    dr.CollectionPointDetail.CollectionPoint.CollectionPointName,
                                    dr.CollectedBy,
                                    dr.DisbursedBy,
                                    dr.CollectionPointDetailID
                                }).OrderByDescending(x => x.CollectionDate);

            obj = disburseList.ToList<object>();
            return obj;

        }

        public int getDeptReqID(string userName, DateTime collDate, String collPointDetID)
        {
            //object obj = new object();

            DeptRequisition deptReq;
            int collPointDetId = Convert.ToInt32(collPointDetID);
            var deptID = (from e in ent.Employees
                          join u in ent.Users
                          on e.UserID equals u.UserID
                          where u.UserName == userName
                          select e.DeptID).SingleOrDefault();

            deptReq = (from dr in ent.DeptRequisitions
                             where dr.DeptID == deptID && dr.CollectionPointDetail.CollectionDate == collDate
                             && dr.CollectionPointDetailID == collPointDetId
                             select dr).SingleOrDefault();

            return deptReq.DeptReqID;
        }

        public List<object> getDDList(int deptReqID)
        {
            List<object> obj = new List<object>();
            var deptReqDetail = (from drd in ent.DeptRequisitionDetails
                                 where drd.DeptReqID == deptReqID
                                 select new { drd.Stationery.ItemName, drd.ActualQty });
            obj = deptReqDetail.ToList<object>();

            return obj;
        }

        public object getDepartmentName(int deptReqID) {
            object obj = new object();
            obj = (from d in ent.Departments
                          join dr in ent.DeptRequisitions
                          on d.DeptID equals dr.DeptID
                          where dr.DeptReqID == deptReqID
                          select d.DeptName).SingleOrDefault();
            return obj;
        }

        public object getRepName(int deptReqID)
        {
            object obj = new object();
            var deptID = (from dr in ent.DeptRequisitions
                          where dr.DeptReqID == deptReqID
                          select dr.DeptID).SingleOrDefault();
            obj = (from d in ent.Departments
                   join e in ent.Employees
                   on d.DeptID equals e.DeptID
                   where d.DeptID == deptID && e.EmployeeID == d.RepresentativeID
                   select e.EmployeeName).SingleOrDefault();
                      
            return obj;
        }

        public object getRepNameCollected(int deptReqID) {
            object obj = new object();

            var collByID = (from dr in ent.DeptRequisitions
                   where dr.DeptReqID == deptReqID
                   select dr.CollectedBy).SingleOrDefault();
            obj = (from e in ent.Employees
                   where e.EmployeeID == collByID
                   select e.EmployeeName).SingleOrDefault();
            return obj;
        }

        public object getRepID(int deptReqID) {
            object obj = new object();
            var deptID = (from dr in ent.DeptRequisitions
                          where dr.DeptReqID == deptReqID
                          select dr.DeptID).SingleOrDefault();
            obj = (from d in ent.Departments
                   where d.DeptID == deptID
                   select d.RepresentativeID).SingleOrDefault();

            return obj;
        }

        public object getEmpID(string userName) {
            object obj = new object();

            obj = (from e in ent.Employees
                   join u in ent.Users
                   on e.UserID equals u.UserID
                   where u.UserName == userName
                   select e.EmployeeID).SingleOrDefault();
            return obj;
        }

        public void addRepName(int deptReqID, int repID) {
            try
            {
                var empID = (from e in ent.Employees
                             where e.EmployeeID == repID
                             select e.EmployeeID).SingleOrDefault();
                DeptRequisition  currentDeptReq = ent.DeptRequisitions.FirstOrDefault(x => x.DeptReqID== deptReqID);
                currentDeptReq.CollectedBy = empID;
                ent.SaveChanges();
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

    }
}
